const router = require('koa-router')()

router.prefix('/correct')

// 生成雪花ID的方法
const createSnow = function (ctx) {
  let snowFlake = ctx.snowFlake;
  let snowId = snowFlake.createSnow(3);
  return snowId;
}

function generateRelations(rows, index = 0, relations = []) {
  if (index === rows.length) {
    return relations;
  }

  const currentRow = rows[index];
  const courseId = currentRow.course_id;
  const assignmentId = currentRow.assignment_id;

  let course = relations.find((r) => r.value === courseId);
  if (!course) {
    course = { value: courseId, label: currentRow.course_name, children: [] };
    relations.push(course);
  }

  let assignment = course.children.find((g) => g.value === courseId);
  if (!assignment) {
    assignment = { value: assignmentId, label: currentRow.assignment_title };
    course.children.push(assignment);
  }

  return generateRelations(rows, index + 1, relations);
}


function generateTeacherRelations(rows, index = 0, relations = []) {
  if (index === rows.length) {
    return relations;
  }

  const currentRow = rows[index];
  const gradeId = currentRow.grade_id;
  const courseId = currentRow.course_id;
  const assignmentId = currentRow.assignment_id;

  let grade = relations.find((r) => r.value === gradeId);
  if (!grade) {
    grade = { value: gradeId, label: currentRow.grade_name, children: [] };
    relations.push(grade);
  }

  let course = grade.children.find((r) => r.value === courseId);
  if (!course) {
    course = { value: courseId, label: currentRow.course_name, children: [] };
    grade.children.push(course);
  }

  let assignment = course.children.find((g) => g.value === assignmentId);
  if (!assignment) {
    assignment = { value: assignmentId, label: currentRow.assignment_title };
    course.children.push(assignment);
  }

  return generateTeacherRelations(rows, index + 1, relations);
}
// 获取学生的课程信息
router.get("/getStudentCourse", async (ctx, next) => {
  let sql = "select course.course_id, course.course_name, assignment.assignment_id, assignment.assignment_title from course join assignment on course.course_id = assignment.course_id";
  let res = await ctx.db.EXCUTE(sql, []);
  // console.log(res);
  // console.log(res);
  // console.log(generateRelations(res), "改变之后课程任务信息");
  let infoList = generateRelations(res);
  // console.log(infoList);
  ctx.body = { code: 200, infoList }
})

// 获取老师的课程信息
router.post("/getTeacherCourse", async (ctx, next) => {
  let sql = "select grade.grade_id, grade.grade_name, course.course_id, course.course_name from grade join course on grade.grade_id = course.grade_id where course.teacher_id = ?"

  let sql1 = "select grade.grade_id, grade.grade_name, course.course_id, course.course_name, assignment.assignment_id, assignment.assignment_title from grade join course on grade.grade_id = course.grade_id join assignment on course.course_id = assignment.course_id where course.teacher_id = ?"
  let res2 = await ctx.db.EXCUTE(sql1, [ctx.request.body.teacher_id]);
  console.log(res2, "45613as");
  let res = await ctx.db.EXCUTE(sql, [ctx.request.body.teacher_id]);

  let sql2 = "select course_id, assignment_id, assignment_title from assignment where course_id in "
  let sqlArr = [];
  sqlArr = res.map(item => {
    return item.course_id
  })
  let res1 = await ctx.db.EXCUTE(sql2 + `(${sqlArr.join(', ')})`, []);
  console.log(res1);
  let arr = res1.map((item, index) => {
    let temp = res.find(i => i.course_id == item.course_id)
    console.log(temp);
    let obj = temp;
    if (temp) {
      obj.assignment_id = item.assignment_id;
      obj.assignment_title = item.assignment_title;
    }
    return obj;
  })
  console.log(arr, "arr");
  console.log(res);
  console.log(generateTeacherRelations(res), "教师信息");
  console.log(generateTeacherRelations(res2));
  let infoList = generateTeacherRelations(res2);

  ctx.body = { code: 200, infoList }
})

// 获取作业信息
router.post('/getAssignmentInfo', async (ctx, next) => {
  // 此处的逻辑应该在前端看到了能够批改，所以才去批阅的，所以说此时的学生是必然提交了作业的
  //  此处需要查询的应该的题目的信息，以及学生的提交的相对应题目的信息（答案）
  let assignment_id = ctx.request.body.assignmentId;
  let student_assignment_id = ctx.request.body.studentAssignmentId;
  let status = ctx.request.body.status;
  let student_correction_id = ctx.request.body.student_correction_id;
  console.log(status, "status");
  console.log(ctx.request.body, "dasdas");
  let sql1 = "select * from assignment where assignment_id = ?";
  let sql6 = "select choice.question_id, choice.choice_question, choice.answer as correct_answer, choice.question_index, choice.isMutiple, choice.proportion, choice.options_list, choice.question_type, choice.proportion, student_submit_radio.answer as student_answer, student_submit_radio.score from student_submit_radio join choice on student_submit_radio.question_id = choice.question_id where student_submit_radio.student_assignment_id = ?";
  let result1 = await ctx.db.EXCUTE(sql6, [student_assignment_id]);
  console.log(result1);
  let result
  if (status === 'edit') {
    let sql5 = "select essayquestion.question_id, essayquestion.essayQuestion, essayquestion.answer as correct_answer, essayquestion.question_index, essayquestion.proportion, essayquestion.question_type, essayquestion.proportion, student_submit_essay.answer as student_answer, student_correction_question.score from student_correction_question join student_submit_essay on student_correction_question.question_id = student_submit_essay.question_id join essayquestion on student_submit_essay.question_id = essayquestion.question_id where student_submit_essay.student_assignment_id = ? and student_correction_question.student_correction_id = ?"
    result = await ctx.db.EXCUTE(sql5, [student_assignment_id, student_correction_id]);
  } else {
    let sql5 = "select essayquestion.question_id, essayquestion.essayQuestion, essayquestion.answer as correct_answer, essayquestion.question_index, essayquestion.proportion, essayquestion.question_type, essayquestion.proportion, student_submit_essay.answer as student_answer, student_submit_essay.score from student_submit_essay join essayquestion on student_submit_essay.question_id = essayquestion.question_id where student_submit_essay.student_assignment_id = ?"
    result = await ctx.db.EXCUTE(sql5, [student_assignment_id]);
  }
  console.log(result, "result");
  let sql4 = "select teacher_name from teacher where teacher_id = ?";
  // 此处应该是assignment_id
  let res1 = await ctx.db.EXCUTE(sql1, [assignment_id]);
  let res4 = await ctx.db.EXCUTE(sql4, [res1[0].teacher_id]);

  let assignmentInfo = res1[0];
  let list = [...result1, ...result];

  list.sort((a, b) => a.question_index - b.question_index);
  assignmentInfo.publish_time = ctx.formTime.formatDate(assignmentInfo.publish_time);
  assignmentInfo.questionList = list;
  assignmentInfo.teacher_name = res4[0].teacher_name;
  console.log(assignmentInfo, "assignment");
  console.log(assignmentInfo, 'sadasd');
  ctx.body = { code: 200, assignmentInfo };

})

router.post('/getStudentCorrection', async(ctx, next) => {
  console.log(ctx.request.body);
  console.log(ctx.query);
  let pageNum = ctx.query.pageNum;
  let pageSize = ctx.query.pageSize;
  let activeName = ctx.request.body.activeName;
  let status
  if(activeName == 'corrected') {
    status = 'review'
  } 
  let sql = "select c.student_id, s1.student_name AS student_name, s2.student_name AS corrected_name, assignment.assignment_title, assignment.total_points, s1.avatar, c.score, c.status, c.correct_time, c.corrected_id, c.isadopt from student_correction_assignment as c join assignment on c.assignment_id = assignment.assignment_id join student as s1 on c.student_id = s1.student_id join student as s2 on c.corrected_id = s2.student_id  where c.assignment_id = ?"
  let res = await ctx.db.EXCUTE(sql, [ctx.request.body.assignmentId]);
  if(activeName == 'corrected') {
    res = res.filter(item => {
      return item.status == 'reviewed'
    })
    res.map(item => {
      item.correct_time = ctx.formTime.formatDate(item.correct_time);
      return item;
    })
  } else {
    res = res.filter(item => {
      return item.status != 'reviewed'
    })
  }
  res.sort((a,b) => {return a.student_id - b.student_id})
  let total = res.length;
  let infoList = res.slice((pageNum - 1) * pageSize, pageNum * pageSize);
  console.log(res);
  ctx.body = {code: 200, infoList, total}
})

router.post("/getStudentSubmit", async (ctx, next) => {
  console.log(ctx.request.body);
  console.log(ctx.query, "query");
  // 首先，一个学生是可以在多个小组，每一个类型小组对应的是的
  console.log(ctx.request.body);
  let assignment_id = ctx.request.body.assignmentId;
  let pageSize = ctx.query.pageSize;
  let pageNum = ctx.query.pageNum;
  let student_id = ctx.request.body.student_id;
  console.log(assignment_id, "assignment_id");
  // 需要呈现的数据应该是小组批改记录
  // 需要展现出其对应小组的信息

  let res;
  if (assignment_id) {
    let state1 = "select sa.student_assignment_id, sc.assignment_id, sc.score, sc.status, sc.student_correction_id, sa.submit_status, sa.submit_time, assignment.assignment_title, assignment.total_points, assignment.correct_deadline, assignment.deadline, sc.corrected_id as student_id, course.course_name from student_correction_assignment as sc join student_submit_assignment as sa on sc.corrected_id = sa.student_id and sc.assignment_id = sa.assignment_id join assignment on sc.assignment_id = assignment.assignment_id join course on assignment.course_id = course.course_id where sc.student_id = ? and sc.assignment_id = ?";
    res = await ctx.db.EXCUTE(state1, [student_id, assignment_id]);
  } else {
    let state1 = "select sa.student_assignment_id, sc.corrected_id, sc.score, sc.student_correction_id, sc.assignment_id, sc.status, sa.submit_status, sa.submit_time, assignment.assignment_title, assignment.total_points, assignment.correct_deadline, assignment.deadline, sc.corrected_id as student_id, course.course_name from student_correction_assignment as sc join student_submit_assignment as sa on sc.corrected_id = sa.student_id and sc.assignment_id = sa.assignment_id join assignment on sc.assignment_id = assignment.assignment_id join course on assignment.course_id = course.course_id where sc.student_id = ?"
    res = await ctx.db.EXCUTE(state1, [student_id]);
  }

  // res = res.filter(item => new Date(item.deadline).getTime() < Date.now() && new Date(item.correct_deadline).getTime() < Date.now());
  console.log(res);
  let total = res.length;
  let infoList = res.slice((pageNum - 1) * pageSize, pageNum * pageSize);
  infoList = infoList.map(item => {
    if (item.submit_time) {
      item.submit_time = ctx.formTime.formatDate(item.submit_time);
    }
    return item;
  })

  ctx.body = { code: 200, infoList, total };
});

// 是否采纳
router.post("/isadopt", async (ctx, next) => {

  let { student_correction_id, sender, recipient_SID, assignment_id } = ctx.request.body
  let msgObj = {}
  console.log(student_correction_id);
  let sql = "select isadopt from student_correction_assignment where student_correction_id = ?";
  let res = await ctx.db.EXCUTE(sql, [student_correction_id]);
  let status;
  if (res[0].isadopt == 'false' || !res[0].isadopt) {
    console.log();
    status = 'true'
  } else if (res[0].isadopt) {
    console.log();
    status = 'false'
  }
  let updateSql = "update student_correction_assignment set isadopt = ? where student_correction_id = ?"
  let res2 = await ctx.db.EXCUTE(updateSql, [status, student_correction_id]);
  console.log(res2);
  if (res2.affectedRows != 0) {
    let sql = 'select user_id from student where student_id = ?'
    let recipient = await ctx.db.EXCUTE(sql, [recipient_SID])
    console.log(recipient);
    let titlesql = 'select assignment_title from assignment where assignment_id = ?'
    let titleres = await ctx.db.EXCUTE(titlesql, [assignment_id])
    let content = status == 'true' ? ('你批改的 ' + titleres[0].assignment_title + ' 作业被采纳') : ('你批改的 ' + titleres[0].assignment_title + ' 作业未被采纳');
    let ssql = 'select message_id from message where sender = ? and recipient = ? and content = ? and assignment_id = ?'
    let sres = await ctx.db.EXCUTE(ssql, [sender, recipient[0].user_id, content, assignment_id])
    if (sres.length == 0) {
      let msql = 'insert into message (message_id,sender,recipient,content,status,create_time,update_time,type,assignment_id) values (?,?,?,?,?,?,?,?,?)'
      let mres = await ctx.db.EXCUTE(msql, [createSnow(ctx), sender, recipient[0].user_id, content, 'unread', formatDate(new Date()), formatDate(new Date()), 'info', assignment_id])
      console.log(mres);
      if (mres.affectedRows != 0) {
        msgObj = { code: 200, msg: '已通知' }
      } else {
        msgObj = { code: 204, msg: 'sql error' }
      }
    } else {
      let msql = 'update message set update_time = ? where message_id = ?'
      let mres = await ctx.db.EXCUTE(msql, [formatDate(new Date()), sres[0].message_id])
      console.log(mres);
      if (mres.affectedRows != 0) {
        msgObj = { code: 200, msg: '已通知' }
      } else {
        msgObj = { code: 204, msg: 'sql error' }
      }
    }
  }
  ctx.body = msgObj
})

// 教师审核获取信息
router.post("/getInfo", async (ctx, next) => {
  // 要需要获取该学生的作业每个批改者对每个题目的批改结果， 只有简答题
  // 首先获取该学生对应的批改信息
  let assignment_id = ctx.request.body.assignment_id;
  let student_assignment_id
  let status = ctx.request.body.status;
  let student_id = ctx.request.body.student_id;
  let student_correction_id_arr
  console.log(status, "status");
  let sql1 = "select * from assignment where assignment_id = ?";
  let res1 = await ctx.db.EXCUTE(sql1, [assignment_id]);
  let sql2 = "select student_assignment_id from student_submit_assignment where student_id = ? and assignment_id = ?";
  let res2 = await ctx.db.EXCUTE(sql2, [student_id, assignment_id]);
  let sql3 = "select student_correction_id from student_correction_assignment where corrected_id = ? and assignment_id = ?";
  let res3 = await ctx.db.EXCUTE(sql3, [student_id, assignment_id]);
  console.log(res2);
  student_assignment_id = res2[0].student_assignment_id;
  student_correction_id_arr = res3;
  let sql6 = "select choice.question_id, choice.choice_question, choice.answer as correct_answer, choice.question_index, choice.isMutiple, choice.proportion, choice.options_list, choice.question_type, choice.proportion, student_submit_radio.answer as student_answer, student_submit_radio.score from student_submit_radio join choice on student_submit_radio.question_id = choice.question_id where student_submit_radio.student_assignment_id = ?";
  let result1 = await ctx.db.EXCUTE(sql6, [student_assignment_id]);
  console.log(result1, "result1");
  let result
  let studentInfo = [];
  if (status === 'edit') {
    let sql5 = "select essayquestion.question_id, essayquestion.essayQuestion, essayquestion.answer as correct_answer, essayquestion.question_index, essayquestion.proportion, essayquestion.question_type, essayquestion.proportion, student_submit_essay.answer as student_answer, student_correction_question.score from student_correction_question join student_submit_essay on student_correction_question.question_id = student_submit_essay.question_id join essayquestion on student_submit_essay.question_id = essayquestion.question_id where student_submit_essay.student_assignment_id = ? and student_correction_question.student_correction_id = ?"
    result = await ctx.db.EXCUTE(sql5, [student_assignment_id, student_correction_id]);
  } else if (status === 'publish') {
    let sql5 = "select essayquestion.question_id, essayquestion.essayQuestion, essayquestion.answer as correct_answer, essayquestion.question_index, essayquestion.proportion, essayquestion.question_type, essayquestion.proportion, student_submit_essay.answer as student_answer, student_submit_essay.score from student_submit_essay join essayquestion on student_submit_essay.question_id = essayquestion.question_id where student_submit_essay.student_assignment_id = ?"
    result = await ctx.db.EXCUTE(sql5, [student_assignment_id]);
  }
  if (res1[0].student_correct == '1') {
    let sql = "select student_correction_assignment.score, student_correction_assignment.isadopt, student_correction_assignment.student_correction_id, student.student_id, student.avatar, student.student_name from student_correction_assignment join student on student_correction_assignment.student_id = student.student_id where corrected_id = ? and assignment_id = ?"
    studentInfo = await ctx.db.EXCUTE(sql, [student_id, assignment_id]);
    console.log(studentInfo, "sadwdasqwe");
    let selectCorrectSql = "select * from student_correction_question where student_correction_id in "
    let sqlArr = [];
    console.log(result, "result");
    console.log(student_correction_id_arr, "dawds");
    student_correction_id_arr.forEach(item => {
      sqlArr.push(item.student_correction_id);
    })
    let questionRes;
    if (sqlArr.length > 0) {
      console.log("执行");
      questionRes = await ctx.db.EXCUTE(selectCorrectSql + `(${sqlArr.join(", ")})`, []);
    }
    console.log(questionRes);
    console.log(result, "ruiohads");
  }

  console.log(result, "result");
  let sql4 = "select teacher_name from teacher where teacher_id = ?";
  let sql5 = "select student_name from student where student_id = ?";
  let res5 = await ctx.db.EXCUTE(sql5, [student_id]);
  // 此处应该是assignment_id
  let res4 = await ctx.db.EXCUTE(sql4, [res1[0].teacher_id]);

  let assignmentInfo = res1[0];
  let list = [...result1, ...result];

  list.sort((a, b) => a.question_index - b.question_index);
  assignmentInfo.publish_time = ctx.formTime.formatDate(assignmentInfo.publish_time);
  assignmentInfo.questionList = list;
  assignmentInfo.teacher_name = res4[0].teacher_name;
  assignmentInfo.student_name = res5[0].student_name;
  assignmentInfo.studentInfo = studentInfo;
  console.log(assignmentInfo, "assignment");
  ctx.body = { code: 200, assignmentInfo };
});

router.post("/getAllStudentSubmit", async (ctx, next) => {
  // 老师批改作业，就是如果说是不允许学生批改，那么就是当提交作业截止之后就可以批改了，如果允许了学生的批改的，那么就是在学生批改时间截止之后能够获取信息进行批改
  // 还未到作业截止时间 以及 还未到批改截止时间
  // 这个地方要进行时间的筛选
  // 分页已完成
  let assignment_id = ctx.request.body.assignmentId;
  let pageSize = ctx.query.pageSize;
  let pageNum = ctx.query.pageNum;
  let status = ctx.request.body.activeName;
  console.log(pageSize, pageNum, "asdasd");
  let sql1 = "select deadline, correct_deadline, student_correct from assignment where assignment_id = ?"
  // 需要完善逻辑， 如果是
  let isshow;
  let result = await ctx.db.EXCUTE(sql1, [assignment_id]);
  if (result[0].student_correct === '0') {
    isshow = Date.now() > new Date(result[0].deadline).getTime();
  } else if (result[0].student_correct === '1') {
    isshow = Date.now() > new Date(result[0].correct_dealine).getTime();
  }
  console.log(result);
  console.log(assignment_id);
  let sql = "select student_submit_assignment.submit_status, student_submit_assignment.student_assignment_id, student_submit_assignment.assignment_id, student_submit_assignment.student_assignment_id, student_submit_assignment.submit_time, student_submit_assignment.correction_status, student_submit_assignment.score, student.avatar, student.student_name, student.student_id, class.class_name from student_submit_assignment join student on student_submit_assignment.student_id = student.student_id join class on student.class_id = class.class_id where student_submit_assignment.assignment_id = ? and student_submit_assignment.submit_status = ?";
  let res = await ctx.db.EXCUTE(sql, [assignment_id, status]);
  let sql2 = "select total_points from assignment where assignment_id = ?";
  let res2 = await ctx.db.EXCUTE(sql2, [assignment_id]);
  console.log(res2);
  console.log(res, "xinfoew");
  let total = res.length;
  let infoList = res.slice((pageNum - 1) * pageSize, pageNum * pageSize);
  console.log(infoList.length, "infoList");
  infoList = infoList.map(item => {
    if (item.submit_time) {
      item.submit_time = ctx.formTime.formatDate(item.submit_time);
      item.total_points = res2[0].total_points;
    }
    return item;
  })

  ctx.body = { code: 200, infoList, total };
});

// 教师批改作业，需要将学会提交记录里面的题目分数进行更新, 需要将correction
router.post('/correctFinish', async (ctx, next) => {
  let data = ctx.request.body.postForm;
  console.log(data, "data");
  let questionList = data.questionList;
  let status = data.status;
  let student_assignment_id = data.student_assignment_id;
  let updateSql = "update student_submit_essay set score = case "
  let sql = "select assignment.total_points from student_submit_assignment join assignment on student_submit_assignment.assignment_id = assignment.assignment_id where student_assignment_id = ?"
  let res = await ctx.db.EXCUTE(sql, [student_assignment_id]);
  let total_points = res[0].total_points;
  let sqlArr = [];
  let allProportion = 0;
  let allScore = 0;
  let score;
  questionList.forEach(item => {
    allProportion += item.proportion;
    allScore += item.score;
    if (item.questionType == '简答题') {
      let sql = `when student_assignment_id = ${student_assignment_id} and question_id = ${item.question_id} then ${item.score}`;
      sqlArr.push(sql);
    }
  })
  score = Number((total_points * (allScore / allProportion)).toFixed(2));
  let updataAssignmentSql = "update student_submit_assignment set correction_status = ?, score = ? where student_assignment_id = ?";
  let res1 = await ctx.db.EXCUTE(updataAssignmentSql, ['true', score, student_assignment_id]);
  console.log(res1);
  if (sqlArr.length > 0) {
    updateSql = updateSql + sqlArr.join(' ') + ' else score end'
    let res = await ctx.db.EXCUTE(updateSql, []);
    console.log(res);
  }
  ctx.body = { code: 200 }
})


// 自动批改选择题, 如果是单选题，那么就是满分，那么如果是多选题的全对给满分，不完整的没分
router.post("/autoCheck", async (ctx, next) => {
  let data = ctx.request.body;

  let sql = "select sr.question_type, sr.answer as student_answer, sr.question_id, choice.answer as correct_answer, sa.student_id, choice.proportion from student_submit_assignment as sa join student_submit_radio as sr on sa.student_assignment_id = sr.student_assignment_id join choice on sr.question_id = choice.question_id where sa.student_assignment_id = ?"

  // 需要同时更新多个表中的分数

  // 构建更新语句的数组
  let updateStatements = [];

  let res = await ctx.db.EXCUTE(sql, [data.id]);

  let updateSql = "update student_submit_radio set score = case question_id "

  res.forEach(element => {
    // 如果题目是单选题
    if (element.question_type == "单选题") {
      let student_answer = JSON.parse(element.student_answer);
      let correct_answer = JSON.parse(element.correct_answer);
      if (correct_answer == student_answer) {
        // 如果答案是正确的，那么就让其更新分数为满分
        let score = element.proportion;
        let updateStatement = `when ${element.question_id} then ${score}`;
        updateStatements.push(updateStatement);
      } else {
        let updateStatement = `when ${element.question_id} then 0`;
        updateStatements.push(updateStatement);
      }
    }
    // 如果是多选题，那么如果选项的被正确答案包含，那么按照答对的选项占正确选项的比例来给分，如果说选项中存在不是正确选项的，那么为0分
    else if (element.question_type == "多选题") {
      let student_answer = JSON.parse(element.student_answer);
      let correct_answer = JSON.parse(element.correct_answer);
      // 检查学生的选项是否全部包含在正确答案中
      // 那么如果学生答案为空，那么直接就是错误
      let isCorrect;
      if (student_answer) {
        isCorrect = student_answer.every(option => correct_answer.includes(option));
      } else {
        isCorrect = false;
      }

      if (isCorrect) {
        // 计算学生选择的选项占正确选项的比例，并乘以题目分值
        let proportion = student_answer.length / correct_answer.length;
        let score = Math.floor(proportion * element.proportion);
        // 构建更新语句并添加到数组中
        let updateStatement = `when ${element.question_id} then ${score}`;
        updateStatements.push(updateStatement);
      } else {
        // 构建更新语句并添加到数组中
        let updateStatement = `when ${element.question_id} then 0`;
        updateStatements.push(updateStatement);
      }
    }
  });

  let updateQuery = updateSql + updateStatements.join(' ') + " ELSE score end";
  let resluts = await ctx.db.EXCUTE(updateQuery, [])

  ctx.body = { code: 200 };
})

router.post("/submitScore", async (ctx, next) => {
  // 完成批改需要做的事情： 将作业的status改为review，然后每个题目的分数加上，然后如果是学生批改，那么就是将学生批改的结果添加到学生的批改记录上面
  // 将学生批改记录中的status改成review，批改时间改成现在，分数改成的每个分数的总和
  // 这个里面的如果是发布的话，那么逻辑不变，如果是编辑，那么需要更新批改记录里面总分数和批改时间，还需要更新题目中的总分数
  let updateSql = "update student_correction_assignment set score = ?, status = ?, correct_time = ? where student_correction_id = ?"
  let data = ctx.request.body.questionList;
  console.log(ctx.request.body, "sadwihoih");
  let student_correction_id = ctx.request.body.student_correction_id;
  let status = ctx.request.body.status;
  let sql = 'insert into student_correction_question (student_correction_id, question_id, score) values '
  let sqlArr = [];
  console.log(data, "data");
  let sql2 = 'update student_correction_question set score = case question_id ';
  let updateArr = [];
  let allProportion = 0;
  // 这个操作应该是老师批改作业的时候进行
  // let updateessaySql = "update student_submit_essay set score = case question_id "
  // data.forEach(item => {
  //   console.log(item);
  //   if(item.questionType == "简答题") {
  //     let updateStatement = `when ${item.question_id} then ${item.score}`;
  //     updateArr.push(updateStatement);
  //   }
  // })
  let allScore = 0;
  if (status === 'publish') {
    data.forEach(item => {
      allProportion += item.proportion;
      console.log(item);
      allScore += item.score;
      if (item.questionType == "简答题") {
        let sql = `(${student_correction_id}, '${item.question_id}', ${item.score})`;
        sqlArr.push(sql);
      }
    })
    if (sqlArr.length > 0) {
      sql += sqlArr.join(', ')
      let res = await ctx.db.EXCUTE(sql, []);
      console.log(res);
    }
  } else if (status === 'edit') {
    data.forEach(item => {
      allProportion += item.proportion;
      console.log(item);
      allScore += item.score;
      if (item.questionType == "简答题") {
        let sql = `when ${item.question_id} then ${Number(item.score)}`;
        updateArr.push(sql);
      }
    })
    if (updateArr.length > 0) {
      sql2 = sql2 + updateArr.join(' ') + " ELSE score end";
      let res = await ctx.db.EXCUTE(sql2, []);
      console.log(res);
    }
  }

  let nowTime = formatDate(new Date());
  console.log(nowTime, "现在时间是");
  console.log(allScore, allProportion, (ctx.request.body.total_points * (allScore/allProportion)).toFixed(2), "sadwa");
  let res = await ctx.db.EXCUTE(updateSql, [Number((ctx.request.body.total_points * (allScore/allProportion)).toFixed(2)), "reviewed", nowTime, student_correction_id]);
  console.log(res);

  ctx.body = { code: 200 }
})

function formatDate(time) {
  const date = new Date(time);
  const year = date.getFullYear();
  const month = (date.getMonth() + 1).toString().padStart(2, '0');
  const day = date.getDate().toString().padStart(2, '0');
  const hours = date.getHours().toString().padStart(2, '0');
  const minutes = date.getMinutes().toString().padStart(2, '0');
  const seconds = date.getSeconds().toString().padStart(2, '0');

  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}

// 评论
router.post('/critic', async (ctx, next) => {
  let { content, sender, recipient_SID, assignment_id } = ctx.request.body
  let sql = 'select user_id from student where student_id = ?'
  let recipient = await ctx.db.EXCUTE(sql, [recipient_SID])
  console.log(recipient);
  let ssql = 'select message_id from message where sender = ? and recipient = ? and content = ?'
  let sres = await ctx.db.EXCUTE(ssql, [sender, recipient[0].user_id, content])
  if (sres.length == 0) {
    let msql = 'insert into message (message_id,sender,recipient,content,status,create_time,update_time,type,assignment_id) values (?,?,?,?,?,?,?,?,?)'
    let mres = await ctx.db.EXCUTE(msql, [createSnow(ctx), sender, recipient[0].user_id, content, 'unread', formatDate(new Date()), formatDate(new Date()), '#', assignment_id])
    console.log(mres);
    if (mres.affectedRows != 0) {
      ctx.body = { code: 200, msg: '已评论' }
    } else {
      ctx.body = { code: 204, msg: 'sql error' }
    }
  } else {
    let msql = 'update message set update_time = ? where message_id = ?'
    let mres = await ctx.db.EXCUTE(msql, [formatDate(new Date()), sres[0].message_id])
    console.log(mres);
    if (mres.affectedRows != 0) {
      ctx.body = { code: 200, msg: '已评论' }
    } else {
      ctx.body = { code: 204, msg: 'sql error' }
    }
  }
})
// 用户获取某任务下的评论
router.post('/getminfo', async (ctx, next) => {
  let { assignment_id } = ctx.request.body
  console.log(assignment_id);
  // 搜索老师发的
  let tsql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, user.user_name, user.user_auth, teacher.avatar, teacher.signature, assignment.assignment_title from message join user on user.user_id = message.sender join teacher on teacher.user_id = message.sender join assignment on assignment.assignment_id = message.assignment_id where message.assignment_id = ? ORDER BY message.update_time DESC'
  let tres = await ctx.db.EXCUTE(tsql, [assignment_id])
  // 搜索学生发的
  let ssql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, user.user_name, user.user_auth, student.avatar, student.signature, assignment.assignment_title from message join user on user.user_id = message.sender join student on student.user_id = message.sender join assignment on assignment.assignment_id = message.assignment_id where message.assignment_id = ? ORDER BY message.update_time DESC'
  let sres = await ctx.db.EXCUTE(ssql, [assignment_id])
  // 查询每条的点赞数
  let nsql = 'select message_id, up_user_id from favour where message_id IN (select message.message_id from message where message.assignment_id = ?)'
  let nres = await ctx.db.EXCUTE(nsql, [assignment_id])
  let tlist = tres.map(item => {
    return {
      ...item,
      num: nres.filter(i => i.message_id == item.message_id).length,
      isliked: nres.filter(i => i.up_user_id == item.recipient && i.message_id == item.message_id).length == 0 ? false : true
    }
  })
  let slist = sres.map(item => {
    return {
      ...item,
      num: nres.filter(i => i.message_id == item.message_id).length,
      isliked: nres.filter(i => i.up_user_id == item.recipient && i.message_id == item.message_id).length == 0 ? false : true
    }
  })
  ctx.body = { code: 200, data: [...tlist, ...slist] }

})


module.exports = router